﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wicresoft.UnifyShow.Business;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class Problem : Basic, IProblem
    {
        public Decimal GetProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where status > 0 and status < 9";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        public List<IncidentAndChangeModel> GetProblemCountGroupTimeBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from problem where status > 0";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        //服务统计 已解决问题， 所有问题
        public List<SiteBranchAndWorkListModel> GetProblemInfoList(int? site, int? bnid, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from problem i inner join sitebranch s on i.siteid = s.id where 1=1";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i.BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketResolvedTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from problem i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status >=10";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and i2.BusinessSystemId = :bnid2";
                cmdParms.Add(new OracleParameter("bnid2", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketResolvedTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<SiteBranchAndWorkListModel> GetProblemInfoListBySystemName(int? site, string systemname, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select t1.alias alls,sb.id siteid,t1.count1 sign,t2.count2 sign2 from (select Count(*) count1,s.alias from problem i inner join sitebranch s on i.siteid = s.id where 1=1";
            if (site != null)
            {
                sqlStr += " and i.SiteId = :siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i.BusinessSystemId in (select id from BusinessSystem  where name = :systemname)";
                cmdParms.Add(new OracleParameter("systemname", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i.TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i.TicketResolvedTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s.alias ) t1 left join (select Count(*) count2,s2.alias from problem i2 inner join sitebranch s2 on i2.siteid = s2.id where i2.status >=10";
            if (site != null)
            {
                sqlStr += " and i2.SiteId = :siteid2";
                cmdParms.Add(new OracleParameter("siteid2", OracleDbType.Int32, site, ParameterDirection.Input));
            }
            if (!string.IsNullOrEmpty(systemname))
            {
                sqlStr += " and i2.BusinessSystemId in (select id from BusinessSystem  where name = :systemname2)";
                cmdParms.Add(new OracleParameter("systemname2", OracleDbType.NVarchar2, systemname, ParameterDirection.Input));
            }
            if (sTime != null)
            {
                sqlStr += " and i2.TicketCreateTime >= :sTime2";
                cmdParms.Add(new OracleParameter("sTime2", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                sqlStr += " and i2.TicketResolvedTime <= :eTime2";
                cmdParms.Add(new OracleParameter("eTime2", OracleDbType.Date, eTime, ParameterDirection.Input));
            }
            sqlStr += " group by s2.alias) t2 on t1.alias =t2.alias left join sitebranch sb on sb.alias=t1.alias";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<SiteBranchAndWorkListModel>.GetModel_By_OracleDataReader(dr);
        }

        public List<IncidentAndChangeModel> GetEscalationProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_number(problemsource) Id from problem  where problemsource <> '0'";
            //string sqlStr = "select Count(*)  from escalation e inner join problem p on e.destinationid = p.request_id  where e.DestinationType = 3";
            //string sqlStr = "select Count(distinct e.sourceid) from escalation e inner join problem p on e.destinationid = p.request_id inner join problem p1 on e.sourceid = p1.request_id where p.siteid = '101' and p1.siteid != '101' and e.DestinationType =3"; 
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by problemsource";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }

        
        public decimal GetClosedProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where status = 8";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        public decimal GetClosedAndCompleteProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where(status = 8 or status = 6)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }

        public List<IncidentAndChangeModel> GetProblemImpactSortCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*) alls,to_char(TicketCreateTime, 'yyyyMM') sign from problem where status > 0 and (Impact = 1000 OR Impact = 2000)";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }


        public List<IncidentAndChangeModel> GetAvgtimeProblemBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select round(avg(DiagnoseTime - TicketCreateTime),4)*24 alls,to_char(TicketCreateTime, 'yyyyMM') sign from problem where status = 8";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            sqlStr += " group by  to_char(TicketCreateTime, 'yyyyMM')";
            OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<IncidentAndChangeModel>.GetModel_By_OracleDataReader(dr);
        }


        public decimal GetProblemFirmCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where  (status = 6 or status = 8) and firmprostatus = 3000";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }


        public decimal GetProblemTimeOutCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where (status = 6 or status = 8) and ResolvedTimeOut > 0 and ResolvedTimeOut is not null";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }


        public decimal GetReasonProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where  (status = 6 or status = 8) and hasprimarycause = 1";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }


        public decimal GetSolveProblemCountBySiteAndBusiness(int? siteid, int? bnid, DateTime? sftime, DateTime? eftime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = "select Count(*)  from problem where  (status = 6 or status = 8) and closecode = 0";
            if (siteid != null)
            {
                sqlStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, siteid, ParameterDirection.Input));
            }
            if (bnid != null)
            {
                sqlStr += " and BusinessSystemId = :bnid";
                cmdParms.Add(new OracleParameter("bnid", OracleDbType.Int32, bnid, ParameterDirection.Input));
            }
            if (sftime != null)
            {
                sqlStr += " and TicketCreateTime >= :sftime";
                cmdParms.Add(new OracleParameter("sftime", OracleDbType.Date, sftime, ParameterDirection.Input));
            }
            if (eftime != null)
            {
                sqlStr += " and TicketCreateTime <= :eftime";
                cmdParms.Add(new OracleParameter("eftime", OracleDbType.Date, eftime, ParameterDirection.Input));
            }
            //OracleDataReader dr = oda.ExecuteReader(sqlStr, cmdParms);
            return ModelHelper<ChangeModel>.GetModelCount_By_DirectSql(sqlStr, cmdParms, oda);
        }


        //查询地市未关闭问题(status <  7)
        public int GetUnResolvedProblemCount(int? site, DateTime? sTime, DateTime? eTime)
        {
            List<OracleParameter> cmdParms = new List<OracleParameter>();
            string sqlStr = string.Empty;
            string whereStr = string.Empty;

            if (site != null)
            {
                whereStr += " and siteid=:siteid";
                cmdParms.Add(new OracleParameter("siteid", OracleDbType.Int32, site, ParameterDirection.Input));
            }

            if (sTime != null)
            {
                whereStr += " and TicketCreateTime >= :sTime";
                cmdParms.Add(new OracleParameter("sTime", OracleDbType.Date, sTime, ParameterDirection.Input));
            }
            if (eTime != null)
            {
                whereStr += " and TicketCreateTime <= :eTime";
                cmdParms.Add(new OracleParameter("eTime", OracleDbType.Date, eTime, ParameterDirection.Input));
            }


            sqlStr = "select count(*) from problem where status < 7 " + whereStr;

            object obj = oda.ExecuteScalar(sqlStr, cmdParms);
            return Convert.ToInt32(obj);
        }
    }
}
